#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2024/1/29 10:33
# @Author : 闫峰
# @File    : get_sku_goor_num.py
from common.setting import ensure_path_sep
from utils.mysqlUtils.db_pool import Database

db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")

class Inventory:

    def __init__(self,sku,wareHouse_id_set):
        self.sku = sku
        self.wareHouse_id_set = wareHouse_id_set
        is_group_sql = """SELECT is_group FROM test_zhcxkj_center_product.rb_product WHERE sku=%s;"""
        is_group_sql_res = db.fetch_all(is_group_sql, (self.sku,))
        self.is_group = is_group_sql_res[0]['is_group']

    def get_local_num(self):
        if self.is_group == 0:
            get_local_good_num = """
            SELECT SUM(good_num) good_num FROM test_zhcxkj_center_warehouse.dynamic_inventory WHERE sku=%s
            and warehouse_id in %s;
            """
            local_good_num = db.fetch_all(get_local_good_num, (self.sku,self.wareHouse_id_set))
            if not local_good_num[0].get('good_num'):
                return 0
            if local_good_num[0].get('good_num'):
                # print(f"{sku}动态库存：{res1[0].get('good_num')}")
                return local_good_num[0].get('good_num')

        else:
            get_child_sku = "SELECT sku,sku_num FROM test_zhcxkj_center_product.rb_product_group_sku " \
                            "WHERE group_sku=%s;"
            get_child_sku_res = db.fetch_all(get_child_sku, (self.sku,))
            if not get_child_sku_res:
                # raise Exception("组合产品库查询不到子sku")
                return 0

            if get_child_sku_res:
                sku_list = []
                for child in get_child_sku_res:
                    filter_good_num = f"""
                        SELECT warehouse_id,FLOOR(good_num/{child.get('sku_num')}) good_num FROM test_zhcxkj_center_warehouse.dynamic_inventory 
                        WHERE sku = %s  
                        and warehouse_id in %s
                        """
                    filter_good_num_res = db.fetch_all(filter_good_num, (child.get('sku'),self.wareHouse_id_set))

                    if filter_good_num_res:
                        # min_good_num = min(d['good_num'] for d in filter_good_num_res)
                        good_num = sum(d['good_num'] for d in filter_good_num_res)
                    else:
                        good_num = 0
                    # dongtaikucun = int(good_num / (child.get('sku_num')))
                    sku_list.append((child.get('sku'), good_num))
                min_sku = sorted(sku_list, key=lambda x: x[1], reverse=False)[0]
                finally_good_num = min_sku[1]
                # print(f"{sku}动态库存：{finally_good_num}")
                return finally_good_num



    def get_overseas_num(self):
        if self.is_group == 0:
            get_local_good_num = """
            SELECT SUM(good_num) good_num FROM test_zhcxkj_center_warehouse.rb_ware_house_product WHERE sku=%s
            and warehouse_id in %s;
            """
            local_good_num = db.fetch_all(get_local_good_num, (self.sku,self.wareHouse_id_set))
            if not local_good_num:
                return 0
            if local_good_num[0].get('good_num'):
                # print(f"{sku}动态库存：{res1[0].get('good_num')}")
                return local_good_num[0].get('good_num')
            else:
                return 0
        else:
            get_child_sku = "SELECT sku,sku_num FROM test_zhcxkj_center_product.rb_product_group_sku " \
                            "WHERE group_sku=%s;"
            get_child_sku_res = db.fetch_all(get_child_sku, (self.sku,))
            if not get_child_sku_res:
                raise Exception("组合产品库查询不到子sku")
            if get_child_sku_res:
                sku_list = []
                for child in get_child_sku_res:
                    filter_good_num = f"""
                        SELECT wareHouse_id,FLOOR(good_num/{child.get('sku_num')}) good_num FROM test_zhcxkj_center_warehouse.rb_ware_house_product 
                        WHERE sku = %s  
                        and wareHouse_id in %s
                        """
                    filter_good_num_res = db.fetch_all(filter_good_num, (child.get('sku'),self.wareHouse_id_set))
                    if filter_good_num_res:
                        good_num = sum(d['good_num'] for d in filter_good_num_res)
                    else:
                        good_num = 0
                    sku_list.append((child.get('sku'), good_num))
                min_sku = sorted(sku_list, key=lambda x: x[1], reverse=False)[0]
                finally_good_num = min_sku[1]
                # print(f"{sku}动态库存：{finally_good_num}")
                return finally_good_num



    def get_total(self):
        local_num = self.get_local_num()
        overseas_num = self.get_overseas_num()
        # print(f"local_num={local_num}")
        # print(f"overseas_num={overseas_num}")
        return local_num+overseas_num


if __name__ == '__main__':
    sku = '8234513'
    ssa ={942, 941, 5766}
    Inventory(sku,ssa).get_overseas_num()